package org.csu.mypetstore.persistence.impl;

import org.csu.mypetstore.domain.Account;
import org.csu.mypetstore.persistence.AccountDao;
import org.csu.mypetstore.persistence.impl.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.AbstractList;
import java.util.ArrayList;
import java.util.List;

public class AccountDaoImpl implements AccountDao {
    private static String GET_ACCOUNT_BY_USERNAME="select * from account where userid=?";
    private static String GET_ACCOUNT_BY_USERNAME_AND_PASSWORD="select * from account where userid=? and password=?";
    private static String INSERT_ACCOUNT="insert into account (userid,email,firstname,lastname,status,addr1,addr2,city,state,zip,country,phone,password) values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
    private static String INSERT_PROFILE="insert into profile (userid,langpref,favcategor,banneropt) values(?,?,?,?)";
    private static String INSERT_SIGN_ON="insert into signon (username,password) values(?,?)";
    private static String UPDATE_ACCOUNT="update account set email=?,firstname=?,lastname=?,status=?,addr1=?,addr2=?,city=?,state=?,zip=?,country=?,phone=?,password=? where userid=?";
    private static String UPDATE_PROFILE="update profile set langpref=?,favcategory=?,banneropt=? where userid=?";
    private static String UPDATE_SIGN_ON="update signon set password=? where username=?";
    private static String GET_STATUS="update account set status=?";
    public Account getAccountByUsername(String s) throws Exception {
        Account account=new Account();
        try{
            Connection conn=DBUtil.getConnection();
            PreparedStatement stm=conn.prepareStatement(GET_ACCOUNT_BY_USERNAME);
            stm.setString(1,s);
            ResultSet rs=stm.executeQuery();

            if(rs.next())
            {
                account.setAddress1(rs.getString("addr1"));
                account.setAddress2(rs.getString("addr2"));
                account.setCity(rs.getString("city"));
                account.setUsername(s);
                account.setCountry(rs.getString("country"));
                account.setEmail(rs.getString("email"));
                account.setFirstName(rs.getString("firstname"));
                account.setLastName(rs.getString("lastname"));
                account.setPassword(rs.getString("zip"));
                account.setPhone(rs.getString("phone"));
                account.setState(rs.getString("state"));
                account.setStatus(rs.getString("status"));
                account.setPassword(rs.getString("password"));
            }
            else {
                account=null;
            }
            DBUtil.close(conn,stm,rs);


        }catch (Exception e)
        {
            e.printStackTrace();
        }
        return account;

    }

    @Override
    public Account getAccountByUsernameAndPassword(Account account) {
        Connection conn=null;
        PreparedStatement stm=null;
        ResultSet rs=null;
        try {
            conn=DBUtil.getConnection();
            stm=conn.prepareStatement(GET_ACCOUNT_BY_USERNAME_AND_PASSWORD);
            stm.setString(1,account.getUsername());
            stm.setString(2,account.getPassword());
            rs=stm.executeQuery();
            if(rs.next())
            {
                account.setAddress1(rs.getString("addr1"));
                account.setAddress2(rs.getString("addr2"));
                account.setCity(rs.getString("city"));
                account.setCountry(rs.getString("country"));
                account.setEmail(rs.getString("email"));
                account.setFirstName(rs.getString("firstname"));
                account.setLastName(rs.getString("lastname"));
                account.setZip(rs.getString("zip"));
                account.setPhone(rs.getString("phone"));
                account.setState(rs.getString("state"));
                account.setStatus(rs.getString("status"));

            }
            else
            {
                account=null;
            }
            DBUtil.close(conn,stm,rs);
        } catch (Exception e) {
            e.printStackTrace();
        }
         return account;
    }


    @Override
    public void insertAccount(Account account) {
        try {
            Connection conn = DBUtil.getConnection();
            PreparedStatement stm = conn.prepareStatement(INSERT_ACCOUNT);
            stm.setString(1, account.getUsername());
            stm.setString(2, account.getEmail());
            stm.setString(3, account.getFirstName());
            stm.setString(4,account.getLastName());
            stm.setString(5,account.getStatus());
            stm.setString(6,account.getAddress1());
            stm.setString(7, account.getAddress2());
            stm.setString(8,account.getCity());
            stm.setString(9,account.getState());
            stm.setString(10,account.getZip());
            stm.setString(11,account.getCountry());
            stm.setString(12,account.getPhone());
            stm.setString(13,account.getPassword());
            stm.executeUpdate();

            DBUtil.close(conn,stm,null);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

        @Override
    public void insertProfile(Account account) {
            try {
                Connection conn=DBUtil.getConnection();
                PreparedStatement stm=conn.prepareStatement(INSERT_PROFILE);
                stm.setString(1,account.getUsername());
                stm.setString(2,account.getLanguagePreference());
                stm.setString(3,account.getFavouriteCategoryId());
//                stm.setString(4,account.);
                stm.setString(4,account.getBannerName());
                stm.executeUpdate();

                DBUtil.close(conn,stm,null);
            } catch (Exception e) {
                e.printStackTrace();
            }


        }

    @Override
    public void insertSignOn(Account account) {
        try {
            Connection conn=DBUtil.getConnection();
            PreparedStatement stm=conn.prepareStatement(INSERT_SIGN_ON);
            stm.setString(1,account.getUsername());
            stm.setString(2,account.getPassword());

            stm.executeUpdate();

            DBUtil.close(conn,stm,null);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @Override
    public void updateAccount(Account account) {
        Connection conn=null;
        PreparedStatement stm=null;
        try {
            conn=DBUtil.getConnection();
            stm= conn.prepareStatement(UPDATE_ACCOUNT);
            stm.setString(1, account.getEmail());
            stm.setString(2,account.getFirstName());
            stm.setString(3,account.getLastName());
            stm.setString(4,account.getStatus());
            stm.setString(5,account.getAddress1());
            stm.setString(6, account.getAddress2());
            stm.setString(7,account.getCity());
            stm.setString(8,account.getState());
            stm.setString(9,account.getZip());
            stm.setString(10,account.getCountry());
            stm.setString(11,account.getPhone());
            stm.setString(12,account.getPassword());
            stm.setString(13,account.getUsername());

            stm.executeUpdate();
            System.out.println(account.getStatus()+"aaaaaa");

            DBUtil.close(conn,stm,null);
        } catch (Exception e) {
            e.printStackTrace();
        }


    }



    @Override
    public void updateProfile(Account account) {
        try {
            Connection conn=DBUtil.getConnection();
            PreparedStatement stm=conn.prepareStatement(UPDATE_PROFILE);

            stm.setString(1,account.getLanguagePreference());
            stm.setString(2,account.getFavouriteCategoryId());
//                stm.setString(4,account.);
            stm.setString(3,account.getBannerName());
            stm.setString(4,account.getUsername());
            stm.executeUpdate();

            DBUtil.close(conn,stm,null);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void updateSignOn(Account account) {
        try {
            Connection conn=DBUtil.getConnection();
            PreparedStatement stm=conn.prepareStatement(UPDATE_SIGN_ON);
            stm.setString(1,account.getPassword());
            stm.setString(2,account.getUsername());

            stm.executeUpdate();

            DBUtil.close(conn,stm,null);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @Override
    public void getStatus() {
        Connection conn=null;
        PreparedStatement stm=null;


        try {

            conn=DBUtil.getConnection();
            stm=conn.prepareStatement(GET_STATUS);

            stm.setString(1,null);
            stm.executeUpdate();
            DBUtil.close(conn,stm,null);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
